// const oracledb = require('oracledb');
import oracledb from 'oracledb';

class OracleDB {
  constructor(config) {
    this.config = {
      user: config.user,
      password: config.password,
      connectString: config.connectString,
      poolMin: config.poolMin || 2,
      poolMax: config.poolMax || 10,
      poolIncrement: config.poolIncrement || 1,
      queueTimeout: 30000,
      poolTimeout: 60
    };
    this.pool = null;
    this.BaseQuery = this.createBaseQuery();
  }

  // 初始化连接池
  async init() {
    try {
      this.pool = await oracledb.createPool(this.config);
      console.log('Oracle 连接池创建成功');
    } catch (err) {
      console.error('创建 Oracle 连接池失败:', err);
      throw err;
    }
  }

  // 获取连接
  async getConnection() {
    if (!this.pool) {
      throw new Error('连接池未初始化，请先调用 init() 方法');
    }
    return await this.pool.getConnection();
  }

  // 执行SQL查询
  async execute(sql, params = [], options = {}) {
    console.log(sql)
    console.log(params)
    let connection;
    try {
      connection = await this.getConnection();
      const result = await connection.execute(sql, params, {
        outFormat: oracledb.OUT_FORMAT_OBJECT,
        autoCommit:true,
        ...options
      });
      return result;
    } catch (err) {
      console.error('执行SQL出错:', err);
      throw err;
    } finally {
      if (connection) {
        try {
          await connection.close();
        } catch (closeErr) {
          console.error('关闭连接出错:', closeErr);
        }
      }
    }
  }

  // 执行事务
  async executeTransaction(callback) {
    let connection;
    try {
      connection = await this.getConnection();
      await connection.beginTransaction();
      const result = await callback(connection);
      await connection.commit();
      return result;
    } catch (err) {
      if (connection) {
        try {
          await connection.rollback();
        } catch (rollbackErr) {
          console.error('回滚事务出错:', rollbackErr);
        }
      }
      throw err;
    } finally {
      if (connection) {
        try {
          await connection.close();
        } catch (closeErr) {
          console.error('关闭连接出错:', closeErr);
        }
      }
    }
  }

  // 创建基础查询构造器
  createBaseQuery() {
    const self = this;
    
    class BaseQuery {
      constructor(table) {
        this.table = table;
        this._fields = ['*'];
        this._where = {};
        this._order = '';
        this._limit = 0;
        this._offset = 0;
        this._params = {};
      }

      select(fields) {
        if (Array.isArray(fields)) {
          this._fields = fields;
        } else if (typeof fields === 'string') {
          this._fields = [fields];
        }
        return this;
      }

      where(conditions) {
        this._where = { ...this._where, ...conditions };
        return this;
      }

      whereRaw(sql, params = {}) {
        this._whereRaw = sql;
        this._params = { ...this._params, ...params };
        return this;
      }

      orderBy(field, direction = 'ASC') {
        this._order = `${field} ${direction.toUpperCase()}`;
        return this;
      }

      limit(value) {
        this._limit = value;
        return this;
      }

      offset(value) {
        this._offset = value;
        return this;
      }

      async first() {
        // this._limit = 1;
        const results = await this.get();
        return results[0] || null;
      }

      async get() {
        let whereClause = '';
        const whereParams = {};
        
        // 处理普通 where 条件
        const whereParts = [];
        Object.keys(this._where).forEach(key => {
          whereParts.push(`${key} = :${key}`);
          whereParams[key] = this._where[key];
        });
        
        // 处理原始 where 条件
        if (this._whereRaw) {
          whereParts.push(this._whereRaw);
        }
        
        if (whereParts.length > 0) {
          whereClause = `WHERE ${whereParts.join(' AND ')}`;
        }
        
        // 构建完整 SQL
        let sql = `SELECT ${this._fields.join(', ')} FROM ${this.table} ${whereClause}`;
        
        if (this._order) {
          sql += ` ORDER BY ${this._order}`;
        }
        
        if (this._limit > 0) {
          sql += ` OFFSET ${this._offset} ROWS FETCH NEXT ${this._limit} ROWS ONLY`;
        }
        
        // 合并参数
        const params = { ...whereParams, ...this._params };
        
        const result = await self.execute(sql, params);
        return result.rows;
      }

      async count() {
        let whereClause = '';
        const whereParams = {};
        
        const whereParts = [];
        Object.keys(this._where).forEach(key => {
          whereParts.push(`${key} = :${key}`);
          whereParams[key] = this._where[key];
        });
        
        if (this._whereRaw) {
          whereParts.push(this._whereRaw);
        }
        
        if (whereParts.length > 0) {
          whereClause = `WHERE ${whereParts.join(' AND ')}`;
        }
        
        const sql = `SELECT COUNT(*) as total FROM ${this.table} ${whereClause}`;
        const result = await self.execute(sql, whereParams);
        return parseInt(result.rows[0].TOTAL, 10);
      }

      async insert(data) {
        const columns = Object.keys(data).join(', ');
        const placeholders = Object.keys(data)
          .map(k => (data[k] && data[k].raw ? data[k].raw : `:${k}`))
          .join(', ');

        // 过滤掉 raw 字段的参数
        const params = {};
        for (const k in data) {
          if (!data[k] || !data[k].raw) params[k] = data[k];
        }

        const sql = `INSERT INTO ${this.table} (${columns}) VALUES (${placeholders})`;
        const result = await self.execute(sql, params);
        if (result.lastRowid) {
          return result.lastRowid;
        }
        return result.rowsAffected;
      }

      async update(data) {
        const setParts = [];
        const params = { ...data };
        
        Object.keys(data).forEach(key => {
          setParts.push(`${key} = :${key}`);
        });
        
        let whereClause = '';
        const whereParts = [];
        Object.keys(this._where).forEach(key => {
          whereParts.push(`${key} = :w_${key}`);
          params[`w_${key}`] = this._where[key];
        });
        
        if (whereParts.length > 0) {
          whereClause = `WHERE ${whereParts.join(' AND ')}`;
        }
        
        const sql = `UPDATE ${this.table} SET ${setParts.join(', ')} ${whereClause}`;
        const result = await self.execute(sql, params);
        return result.rowsAffected;
      }

      async delete() {
        let whereClause = '';
        const whereParts = [];
        const params = {};
        
        Object.keys(this._where).forEach(key => {
          whereParts.push(`${key} = :${key}`);
          params[key] = this._where[key];
        });
        
        if (whereParts.length > 0) {
          whereClause = `WHERE ${whereParts.join(' AND ')}`;
        }
        
        const sql = `DELETE FROM ${this.table} ${whereClause}`;
        const result = await self.execute(sql, params);
        return result.rowsAffected;
      }
    }
    
    return BaseQuery;
  }

  // 关闭连接池
  async close() {
    try {
      if (this.pool) {
        await this.pool.close();
        this.pool = null;
        console.log('Oracle 连接池已关闭');
      }
    } catch (err) {
      console.error('关闭 Oracle 连接池出错:', err);
      throw err;
    }
  }

  // 快捷方法
  table(tableName) {
    return new this.BaseQuery(tableName);
  }
}

// module.exports = OracleDB;
export default OracleDB;